<?php
//将Excel表格的东西导入数据库，注意单引号要替换为“‘’”
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Shanghai');
define('DBASE','lab_achievement');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';//downloading
require_once('newmysqlSetup.php');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
include 'PHPExcel/Worksheet/ColumnDimension.php';
$inputFileType = 'Excel5';
//	$inputFileType = 'Excel2007';
//read
$inputFileName = './source.xls';//name of file you‘ll read

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objPHPExcel->setActiveSheetIndex(2);//选择工作区域
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$link=LinkMysql();
$insert_count=0;
$table_index=array('C'=>'标题','D'=>'标题','E'=>'原文语言','H'=>'论文类型','I'=>'被发表于','J'=>'被发表于','K'=>'被发表于','P'=>'检索号','M'=>'会议地点',
				   'T'=>'基金');
$number=array('一','二','三','四','五');
//作者中英文对照
$author_en_ch=array(
    "xiangyang" =>  "龚向阳",
    "wendong"   =>  "王文东",
    "shiduan"   =>  "程时端",
    "xirong"    =>  "阙喜戎",
    "tianye"    =>  "田野",   "yetian"		=>	"田野",
    "yongping"  =>  "熊永平",
    "yuehui"    =>  "金跃辉",
    "hanlin"    =>  "孙韩林",
    "majian"    =>  "马建",   "jianma"		=>	"马建",
    "chenglong" =>  "程龙",   "longcheng"	=>	"程龙",
    "tianke"    =>  "田克",   "ketian"		=>	"田克",
    "kuifei"    =>  "于魁飞",
    "jiujun"    =>  "程久军",
    "yuhong"    =>  "李玉宏",
	"yidong"	=>	"崔毅东",
	"yangtan"	=>	"杨谈",	  "tanyang"		=>	"杨谈",
	"cuiyong"	=>	"崔勇",	  "yongcui"		=>	"崔勇",
	"canfeng"	=>	"陈灿峰",
	"sunyi"		=>	"孙一",	  "yisun"		=>	"孙一",
);
//
function paperExists($author,$title,$show_sql=false)
{
    global $link;
    $author[0]=strtolower($author[0]);
    $author[1]=strtolower($author[1]);
    $title[0]=strtolower($title[0]);
    $title[1]=strtolower($title[1]);
    $author[0]=sqlFilter($author[0],true);
    $author[1]=sqlFilter($author[1],true);
    $title[0]=sqlFilter($title[0],true);
    $title[1]=sqlFilter($title[1],true);
    $sql="SELECT * FROM lab_achievement WHERE (lower(标题) LIKE '%$title[0]%' OR lower(标题) LIKE '%$title[1]%') 
    AND (lower(第一作者) LIKE '%$author[0]%' OR lower(第一作者) LIKE '%$author[1]%')";
    $ret=MysqlQuery($link,"lab_achievement",$sql);
    if (! $ret) {
        // for debug
        echo "Sql:".$sql.EOL;
        echo "Query failed at line:".__LINE__.".".$link->error.EOL;
        exit;
    }

	echo ($show_sql?$sql.EOL:"");

    return $ret->num_rows;
}

function sqlFilter($str,$search=FALSE)
{
    global $link;
    $str=trim($str);
    if($search){
        $str=preg_replace("/\\\\/","\\\\\\\\",$str);
    }
    $str=$link->real_escape_string($str);
    if($search){
        $str=preg_replace("/_/","\\_",$str);	//因为real_escape_string函数不会对下划线和%进行转义
        $str=preg_replace("/%/","\\%",$str);
    }
    return $str;
}

function  getAuthorCh($author_en)
{
    $author_en=trim($author_en);//去除空格
    $temp=$author_en;
    if(preg_match("/^\\w{2,5}\\s+?\\w{2,5}$/", $temp,$result))//两个字的名字
    {
       $temp=preg_replace("/\\s+/", "", $temp);//去掉里面的空格
    }else if(preg_match("/^(\\w{2,5})\\s+?(\\w{5,})$/",  $temp,$result) || preg_match("/^(\\w{5,})\\s+?(\\w{2,5})$/", $author_en,$result))
    {
        //similar to xx xxxxxx  xxxxx xx
        $temp=strlen($result[1])>=6?$result[1]:$result[2];
    }else{
        return $author_en;
    }
    global $author_en_ch;
    $temp=strtolower($temp);//变为小写
    if(array_key_exists($temp, $author_en_ch)){
        return $author_en_ch[$temp];//返回中文
    }else{
        return $author_en;//返回英文
    }
}

$sql="DELETE FROM `lab_achievement` WHERE 1";
MysqlQuery($link,DBASE,$sql);//delete......................................................................

foreach ($sheetData as $row_index=>$col_array) 
{
	
	if($row_index<=2)
		continue;
	$sql="INSERT INTO lab_achievement ";//(标题,第一作者,第二作者,第三作者,第四作者,第五作者,通信)
	$columns="";
	$values="";
	$valid=true;
	$first_author=null;
	$title[0]=empty($col_array['C'])?$col_array['D']:$col_array['C'];
	$title[1]=empty($col_array['D'])?$col_array['C']:$col_array['D'];
	$last_info="";
	if(stristr($col_array['P'],"20102613044730")){
			/*
				print_r($title);
			echo EOL;
			*/
	}
	foreach($col_array as $index=>$val)
	{
		/*
		 * if(empty($val))
			continue;
		 * */
		 
		$val=sqlFilter($val);
		
		switch($index)
		{
			case 'C':
				$columns='标题';
				$values="'".$val.'__';//———分割中英文名
				if(empty($val) && empty($col_array['D'])){$valid=FALSE;}//没有标题
				break;
			case 'D':
				$values.=$val."'";
				break;
			case 'F'://作者英文名
			$val=preg_replace("/，/", ",", $val);
			$last_info=explode(',',$val);
			break;
			case 'G'://作者中文名
			if(!empty($last_info[0]) || !empty($val)){//如果作者不为空
    			$val=preg_replace("/,/", "，", $val);
    			$authors=explode('，',$val);
    			$i=0;
    			$author_number=count($last_info)>count($authors)?count($last_info):count($authors);
    			for($j=0;$i<$author_number;++$i,++$j)
    			{
    			   if(empty($last_info[$i])){$last_info[$i]="";}//没有的用空格补充
    			   if(empty($authors[$i]) && !empty($last_info[$i])){
    			       $authors[$i]=getAuthorCh($last_info[$i]);//如果没有中文名，获取中文名
    			   }else if(empty($authors[$i])){
					   --$j;
					   continue;
				   }
    				if($j<5)
    				$columns.=",第".$number[$i]."作者";
    				else if($j==5)
    				$columns.=",其他作者";
    				else{
    					$values.="，".$last_info[$i]."__".$authors[$i];
    					continue;
    				}
    				
    				if($j==0){
    				    $first_author[]=empty($authors[0])?$last_info[0]:$authors[0];
    				    $first_author[]=empty($last_info[0])?$authors[0]:$last_info[0];
    				}
    				
    				$values.=",'".$last_info[$i]."__".$authors[$i];
    				$values.=$j<5?"'":"";
    			}
    			$values.=$j>5?"'":"";
    			$last_info="";
			}else{
			    $valid=false;
			}
			break;
			case 'P':
			    $columns.=",".$table_index[$index];
			    $values.=",'";
			    if(!empty($col_array['O'])&&strlen($col_array['O'])>4){
			        $values.="SCI: ".sqlFilter($col_array['O']);
			    }
			    if(!empty($col_array['P']) && strlen($col_array['P'])>10){
			        $values.="　　EI:".sqlFilter($col_array['P']);
			    }
			    $values.="'";
			    break;
			case 'E':
			case 'H':	
			case 'M':
			case 'T':
				$columns.=','.$table_index[$index];
				$values.=",'".$val."'";
				if(stristr($col_array['P'],"20110613649191")){
					//echo "ADD:$val".EOL;
				}
				break;
			case 'J':
			    $last_info=sqlFilter($col_array['I']);
			   if(!empty($val) || !empty($last_info)){
			  
    				$columns.=',期刊会议名';
    				$values.=",'".$val.'__';//分割期刊中英文名,先英后中
    				$values.=$last_info."'";
    				
			   }
			   $last_info="";
			   break;
			case 'K':
			    if(!empty($val)){
    				$columns.=',期刊会议名';
    				$values.=",'".$val."__'";//分割会议中英文名,暂时只有会议名
			    }
				break;
			case 'L'://发表时间和卷期
				//发表时间
				if(preg_match('/(\d{2,4})\s*?年/',$val,$result) ){
					$columns.=",发表时间";
					$values.=","."'".$result[1];
						if(preg_match('/(\d{1,2})\s*?月/',$val,$result)){
							if(intval($result[1])<10){
								$result[1]="0".$result[1];
							}
							$values.="-".$result[1];
							if($count=preg_match('/(?:(\d{1,2}\s*?)-(\s*?\d{1,2}))|(\d{1,2})\s*?日/',$val,$result)){
								if(!empty($result[3])){//只有一个日期
									if(intval($result[3])<10){
										$result[2]="0".$result[2];
									}
									$values.="-".$result[3]."'";
								}
								else{
									if(intval($result[1])<10){
										$result[1]="0".$result[1];
									}
									if(intval($result[2])<10){
										$result[2]="0".$result[2];
									}
									$values.="-".$result[1]."-".$result[2]."'";
								}
							}
							else{
								$values	.= "'";
							}
							
						}
						else{
								$values	.= "'";
						}
						
				}
				//卷
				if(preg_match('/(\d)\s*?卷/',$val,$result)){
					$columns.=',卷';
					$values.=','.intval($result[1]);
				}
				//期
				if(preg_match('/(\d)\s*?期/',$val,$result)){
					$columns.=',期';
					$values.=','.intval($result[1]);
				}
				break;
			case 'N'://页码
			if(preg_match('/(\d+?\s*?-\s*?\d+|\d+)/',$val,$result)){
				$columns.=',页码';
				$values.=",'".$result[1]."'";
			}	
			break;
			default:break;		
		}
		
		if(!$valid){break;}
		
	}
	
	
	/*
		if(stristr($col_array['P'],"20102613044730")){
		print_r($col_array);
		echo EOL;
		$show_sql=true;	//echo "sqlf:$val".EOL;
	}else{
		$show_sql=false;
	}
	*/

	$show_sql=false;
	
	
	if(!empty($columns) && $valid && !paperExists($first_author, $title,$show_sql)){
			$sql.="($columns) value ($values)";
			
			$ret=MysqlQuery($link,DBASE,$sql);
			if(!$ret)
			{
				echo "sql:".$sql.EOL;
				echo 'Insert failed:'.$link->error.EOL;
			}
			else{
				$insert_count+=1;
			}
	}
	else{
	    echo "<pre>Invalid:<br/>";
	    print_r($col_array);
	    echo "</pre>";
	}
}
	echo "<font color='red'>Insert:$insert_count</font>".EOL;
?>